﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Configuration;


namespace ProductDB
{
    public partial class AddBD : Form
    {
        //private SqlConnection sqlConnection;
        private SQLiteConnection sqliteConnection;
        public_method pm = new public_method();

        string Email = String.Empty;

        public AddBD(string email)
        {
            InitializeComponent();
            string connectionString = ConfigurationManager.ConnectionStrings["PDATA"].ConnectionString;
            //sqlConnection = new SqlConnection(connectionString);
            //sqlConnection.Open();

            sqliteConnection = new SQLiteConnection(connectionString);
            sqliteConnection.Open();

            //label6.Text = "Подключен к БД: " + sqlConnection.ConnectionString;
            label6.Text = "Подключен к БД: " + sqliteConnection.ConnectionString;
            this.FormClosed += new FormClosedEventHandler(Form1_Close); //для закрытия скрытой формы1
                                                                        //данная функция нужна для избежания утечек памяти

            Email = email;
            //label7.Text = "Здравствуйте " + pm.GetName(email, sqlConnection);
            //label14.Text = "Здравствуйте " + pm.GetName(email, sqlConnection);
            //label21.Text = "Здравствуйте " + pm.GetName(email, sqlConnection);
            label7.Text = "Здравствуйте " + pm.GetName(email, sqliteConnection);
            label14.Text = "Здравствуйте " + pm.GetName(email, sqliteConnection);
            label21.Text = "Здравствуйте " + pm.GetName(email, sqliteConnection);
        }

        //функция которая закрывает приложение
        private void Form1_Close(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }

        //Добавление в таблицу текущего ремонта
        /*
        private void button1_Click(object sender, EventArgs e)
        {
            
            if (!(textBox1.Text == string.Empty) && !(textBox5.Text == string.Empty))
            {
                if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                int role = pm.GetRole(Email, sqlConnection);
                string person = pm.GetName(Email, sqlConnection);
                try
                {
                    SqlCommand command = new SqlCommand(
                        "INSERT INTO [current_repairs] (product_code, number_of_product, delivery, elimination, date, person, note, role)" +
                        " VALUES (@product_code, @number_of_product, @delivery, @elimination, @date, @person, @note, @role)", sqlConnection);
                    command.Parameters.AddWithValue("product_code", textBox1.Text);
                    command.Parameters.AddWithValue("number_of_product", Convert.ToInt32(textBox2.Text));
                    command.Parameters.AddWithValue("delivery", textBox3.Text);
                    command.Parameters.AddWithValue("elimination", textBox4.Text);
                    command.Parameters.AddWithValue("date", DateTime.Now.Date);
                    command.Parameters.AddWithValue("person", person);
                    command.Parameters.AddWithValue("note", textBox5.Text);
                    command.Parameters.AddWithValue("role", role);
                    command.ExecuteNonQuery();

                    textBox2.Text = String.Empty;
                    textBox3.Text = String.Empty;
                    textBox4.Text = String.Empty;

                    label6.BackColor = Color.LightSkyBlue;
                    label6.Text = "Текущий ремонт: данные успешно добавлены " + DateTime.Now.ToString(); 
                    sqlConnection.Close();
                }
                catch (Exception ex)
                {
                    label6.BackColor = Color.Red;
                    label6.Text = "Данные не добавлены " + DateTime.Now.ToString();
                    MessageBox.Show(ex.Message);
                    sqlConnection.Close();
                }


            }
            else
            {
                MessageBox.Show("Обязательны к заполнению строки:" +
                    "Код продукта и Описание причины отказа пожалуйста заполните их...");
            }
        }*/
        private void button1_Click(object sender, EventArgs e)
        {
            if (!(textBox1.Text == string.Empty) && !(textBox5.Text == string.Empty))
            {
                if (sqliteConnection.State == ConnectionState.Closed)
                {
                    sqliteConnection.Open();
                }

                int role = pm.GetRole(Email, sqliteConnection);
                string person = pm.GetName(Email, sqliteConnection);
                // Убираем символы новой строки из текста
                string cleanedText = textBox5.Text.Replace("\r", " ").Replace("\n", " ");
                try
                {
                    SQLiteCommand command = new SQLiteCommand(
                        "INSERT INTO [current_repairs] (product_code, number_of_product, delivery, elimination, date, person, note, role)" +
                        " VALUES (@product_code, @number_of_product, @delivery, @elimination, @date, @person, @note, @role)", sqliteConnection);
                    command.Parameters.Add("@product_code", DbType.String).Value = textBox1.Text.ToUpper();
                    command.Parameters.Add("@number_of_product", DbType.Int32).Value = Convert.ToInt32(textBox2.Text);
                    command.Parameters.Add("@delivery", DbType.String).Value = textBox3.Text;
                    command.Parameters.Add("@elimination", DbType.String).Value = textBox4.Text;
                    command.Parameters.Add("@date", DbType.DateTime).Value = DateTime.Now.Date;
                    command.Parameters.Add("@person", DbType.String).Value = person;
                    command.Parameters.Add("@note", DbType.String).Value = cleanedText;
                    command.Parameters.Add("@role", DbType.Int32).Value = role;
                    command.ExecuteNonQuery();

                    textBox2.Text = String.Empty;
                    textBox3.Text = String.Empty;
                    textBox4.Text = String.Empty;

                    label6.BackColor = Color.LightSkyBlue;
                    label6.Text = "Текущий ремонт: данные успешно добавлены " + DateTime.Now.ToString();
                    sqliteConnection.Close();
                }
                catch (Exception ex)
                {
                    label6.BackColor = Color.Red;
                    label6.Text = "Данные не добавлены " + DateTime.Now.ToString();
                    MessageBox.Show(ex.Message);
                    sqliteConnection.Close();
                }
            }
            else
            {
                MessageBox.Show("Обязательны к заполнению строки:" +
                    "Код продукта и Описание причины отказа пожалуйста заполните их...");
            }
        }

        //добавление в таблицу гарантийного ремонта
        /*
        private void button2_Click(object sender, EventArgs e)
        {
            if(!(textBox6.Text == string.Empty) && !(textBox7.Text == string.Empty) &&
                !(textBox8.Text == string.Empty) && !(textBox9.Text == string.Empty))
            {
                if (dateTimePicker1.Value.Date < DateTime.Now.Date)
                {
                    if (sqlConnection.State == ConnectionState.Closed)
                    {
                        sqlConnection.Open();
                    }
                    int role = pm.GetRole(Email, sqlConnection);
                    string person = pm.GetName(Email, sqlConnection);

                    try
                    {
                        SqlCommand command = new SqlCommand(
                            "INSERT INTO [warranty_repair] (product_code, number_of_product, year_of_release, act_number, date, person, organization, note, role)" +
                            "VALUES (@product_code, @number_of_product, @year_of_release, @act_number, @date, @person, @organization, @note, @role)", sqlConnection);
                        command.Parameters.AddWithValue("product_code", textBox6.Text);
                        command.Parameters.AddWithValue("number_of_product", Convert.ToInt32(textBox7.Text));
                        command.Parameters.AddWithValue("year_of_release", dateTimePicker1.Value.Date);
                        command.Parameters.AddWithValue("act_number", textBox8.Text);
                        command.Parameters.AddWithValue("date", DateTime.Now.Date);
                        command.Parameters.AddWithValue("person", person);
                        command.Parameters.AddWithValue("organization", textBox9.Text);
                        command.Parameters.AddWithValue("note", textBox10.Text);
                        command.Parameters.AddWithValue("role", role);

                        command.ExecuteNonQuery();

                        textBox7.Text = String.Empty;
                        label6.BackColor = Color.LightSkyBlue;
                        label6.Text = "Гарантийный ремонт: данные успешно добавлены " + DateTime.Now.ToString();
                        sqlConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        label6.BackColor = Color.Red;
                        label6.Text = "Данные не добавлены " + DateTime.Now.ToString();
                        MessageBox.Show(ex.Message);
                        sqlConnection.Close();
                    }

                }
                else
                {
                    MessageBox.Show("Не корректная дата выпуска! Минимально возможный день: " + DateTime.Now.Date.AddDays(-1).ToString("dd.MM.yyyy"));
                    dateTimePicker1.Value = DateTime.Now.Date.AddDays(-1); // Сбросить значение на вчерашнюю дату
                    sqlConnection.Close();
                }
            }
            else
            {
                MessageBox.Show("Обязательны к заполнению строки:" +
                    "Код продукта, № Акта, Организация. Пожалуйста заполните их...");
            }
        }*/
        private void button2_Click(object sender, EventArgs e)
        {
            if (!(textBox6.Text == string.Empty) && !(textBox7.Text == string.Empty) &&
               !(textBox8.Text == string.Empty) && !(textBox9.Text == string.Empty))
            {
                if (dateTimePicker1.Value.Date <= DateTime.Now.Date)
                {
                    if (sqliteConnection.State == ConnectionState.Closed)
                    {
                        sqliteConnection.Open();
                    }
                    int role = pm.GetRole(Email, sqliteConnection);
                    string person = pm.GetName(Email, sqliteConnection);
                    // Убираем символы новой строки из текста
                    string cleanedText = textBox10.Text.Replace("\r", " ").Replace("\n", " ");
                    try
                    {
                        SQLiteCommand command = new SQLiteCommand(
                            "INSERT INTO [warranty_repair] (product_code, number_of_product, year_of_release, act_number, date, person, organization, note, role)" +
                            "VALUES (@product_code, @number_of_product, @year_of_release, @act_number, @date, @person, @organization, @note, @role)", sqliteConnection);
                        command.Parameters.Add("@product_code", DbType.String).Value = textBox6.Text.ToUpper();
                        command.Parameters.Add("@number_of_product", DbType.Int32).Value = Convert.ToInt32(textBox7.Text);
                        command.Parameters.Add("@year_of_release", DbType.DateTime).Value = dateTimePicker1.Value.Date;
                        command.Parameters.Add("@act_number", DbType.String).Value = textBox8.Text;
                        command.Parameters.Add("@date", DbType.DateTime).Value = DateTime.Now.Date;
                        command.Parameters.Add("@person", DbType.String).Value = person;
                        command.Parameters.Add("@organization", DbType.String).Value = textBox9.Text;
                        command.Parameters.Add("@note", DbType.String).Value = cleanedText;
                        command.Parameters.Add("@role", DbType.Int32).Value = role;

                        command.ExecuteNonQuery();

                        textBox7.Text = String.Empty;
                        label6.BackColor = Color.LightSkyBlue;
                        label6.Text = "Гарантийный ремонт: данные успешно добавлены " + DateTime.Now.ToString();
                        sqliteConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        label6.BackColor = Color.Red;
                        label6.Text = "Данные не добавлены " + DateTime.Now.ToString();
                        MessageBox.Show(ex.Message);
                        sqliteConnection.Close();
                    }

                }
                else
                {
                    MessageBox.Show("Не корректная дата выпуска! Минимально возможный день: " + DateTime.Now.Date.AddDays(-1).ToString("yyyy-MM-dd"));
                    dateTimePicker1.Value = DateTime.Now.Date.AddDays(-1); // Сбросить значение на вчерашнюю дату
                    sqliteConnection.Close();
                }
            }
            else
            {
                MessageBox.Show("Обязательны к заполнению строки:" +
                    "Код продукта, № Акта, Организация. Пожалуйста заполните их...");
            }
        }


        //Добавление в таблицу ежинедельной проверки
        /*
        private void button6_Click(object sender, EventArgs e)
        {
            if(!(textBox11.Text == String.Empty))
            {
                if(dateTimePicker2.Value.Date < DateTime.Now.Date)
                {
                    if (sqlConnection.State == ConnectionState.Closed)
                    {
                        sqlConnection.Open();
                    }
                    int role = pm.GetRole(Email, sqlConnection);
                    string person = pm.GetName(Email, sqlConnection);

                    try
                    {
                        SqlCommand command = new SqlCommand(
                            "INSERT INTO [one_week_check] (product_code, number_of_product, date, date_make, corresponds, person, note, role)" +
                            " VALUES (@product_code, @number_of_product, @date, @date_make, @corresponds, @person, @note, @role)", sqlConnection);
                        command.Parameters.AddWithValue("product_code", textBox11.Text);
                        command.Parameters.AddWithValue("number_of_product", Convert.ToInt32(textBox12.Text));
                        command.Parameters.AddWithValue("date", DateTime.Now.Date);
                        command.Parameters.AddWithValue("date_make", dateTimePicker2.Value.Date);
                        command.Parameters.AddWithValue("corresponds", textBox13.Text);
                        command.Parameters.AddWithValue("person", person);
                        command.Parameters.AddWithValue("note", textBox14.Text);
                        command.Parameters.AddWithValue("role", role);

                        command.ExecuteNonQuery();

                        textBox12.Text = String.Empty;
                        label6.BackColor = Color.LightSkyBlue;
                        label6.Text = "Еженедельная проверка: данные успешно добавлены " + DateTime.Now.ToString();
                        sqlConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        label6.BackColor = Color.Red;
                        label6.Text = "Данные не добавлены " + DateTime.Now.ToString();
                        MessageBox.Show(ex.Message);
                        sqlConnection.Close();
                    }
                }
                else
                {
                    MessageBox.Show("Не корректная дата изготовления! Минимально возможный день: " + DateTime.Now.Date.AddDays(-1).ToString("dd.MM.yyyy"));
                    dateTimePicker2.Value = DateTime.Now.Date.AddDays(-1); // Сбросить значение на вчерашнюю дату
                    sqlConnection.Close();
                }

            }
            else
            {
                MessageBox.Show("Поле: Код продукта, не должно быть пустым!");
            }
        }*/
        private void button6_Click(object sender, EventArgs e)
        {
            if (!(textBox11.Text == String.Empty))
            {
                if (dateTimePicker2.Value.Date <= DateTime.Now.Date)
                {
                    if (sqliteConnection.State == ConnectionState.Closed)
                    {
                        sqliteConnection.Open();
                    }
                    int role = pm.GetRole(Email, sqliteConnection);
                    string person = pm.GetName(Email, sqliteConnection);
                    // Убираем символы новой строки из текста
                    string cleanedText = textBox14.Text.Replace("\r"," ").Replace("\n"," ");
                    try
                    {
                        SQLiteCommand command = new SQLiteCommand(
                            "INSERT INTO [one_week_check] (product_code, number_of_product, date, date_make, corresponds, person, note, role)" +
                            " VALUES (@product_code, @number_of_product, @date, @date_make, @corresponds, @person, @note, @role)", sqliteConnection);
                        command.Parameters.Add("@product_code", DbType.String).Value = textBox11.Text.ToUpper();
                        command.Parameters.Add("@number_of_product", DbType.Int32).Value = Convert.ToInt32(textBox12.Text);
                        command.Parameters.Add("@date", DbType.DateTime).Value = DateTime.Now.Date;
                        command.Parameters.Add("@date_make", DbType.DateTime).Value = dateTimePicker2.Value.Date;
                        command.Parameters.Add("@corresponds", DbType.String).Value = textBox13.Text;
                        command.Parameters.Add("@person", DbType.String).Value = person;
                        command.Parameters.Add("@note", DbType.String).Value = cleanedText;
                        command.Parameters.Add("@role", DbType.Int32).Value = role;

                        command.ExecuteNonQuery();

                        textBox12.Text = String.Empty;
                        label6.BackColor = Color.LightSkyBlue;
                        label6.Text = "Еженедельная проверка: данные успешно добавлены " + DateTime.Now.ToString();
                        sqliteConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        label6.BackColor = Color.Red;
                        label6.Text = "Данные не добавлены " + DateTime.Now.ToString();
                        MessageBox.Show(ex.Message);
                        sqliteConnection.Close();
                    }
                }
                else
                {
                    MessageBox.Show("Не корректная дата изготовления! Минимально возможный день: " + DateTime.Now.Date.AddDays(-1).ToString("yyyy-MM-dd"));
                    dateTimePicker2.Value = DateTime.Now.Date.AddDays(-1); // Сбросить значение на вчерашнюю дату
                    sqliteConnection.Close();
                }

            }
            else
            {
                MessageBox.Show("Поле: Код продукта, не должно быть пустым!");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            BDViewsDG dViewsDG = new BDViewsDG(Email);
            dViewsDG.ShowDialog();
        }
    }
}
